﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using Adex;
using Telerik.Web.UI;
using SubSonic.Sugar;
using System.Security;
using MediaMonitor.Common;
using Ionic.Zip;
using System.IO;
using OfficeOpenXml;
using OfficeOpenXml.Style;
using System.Drawing;
using OfficeOpenXml.Drawing.Chart;



using Telerik.Charting;
using Telerik.Charting.Styles;
using System.Transactions;
using System.Web.UI.HtmlControls;

namespace MediaMonitor.Users
{
    public partial class Report_NewAlertToday : Adex.BasePage
    {
        string Date = "";
        string code = "";
        string Calificacion = "";
        string Company = "";
        protected void Page_Init(object sender, EventArgs e)
        {
        }
        protected void Page_Load(object sender, EventArgs e)
        {
            if (Session["Company"] == null || Session["CompanyCode"]==null)
            {
                string retUrl = null;
                retUrl = Utility.GetRequestedVal("ReturnUrl", "");
                Response.Redirect("../login.aspx");

            }
            DataTable dt = SPs.Getlogoname(Convert.ToInt32(Session["CompanyCode"])).GetDataSet().Tables[0];
           
            imgLogo.Src = "../get-thumb.aspx?f=images/companies/tn/" + dt.Rows[0]["Logo"].ToString()+"&w=70";
            if (Request.QueryString["Date"] != "" && Request.QueryString["Date"] != null)
            {
                Date = Request.QueryString["Date"];
                Session["code"] = "";
            }
            else
            {
                Date = "";
               // Session["code"] = "last";
                Calificacion = "";
            }
            if (!IsPostBack)
            {
                if (Request.Cookies["NoSummary"].Value.ToString() == "True")
                {

                    Inngrid();
                    //objDS.SelectParameters["MediaTypeCode"].DefaultValue = MediaType.ToString();
                    //objDSExport.SelectParameters["MediaTypeCode"].DefaultValue = MediaType.ToString();
                    //Session["mtc"] = Convert.ToInt32(MediaType.ToString());

                    radGrid.DataSourceID = "objDS";

                    //PrepareGridColumns();
                    radGridAll.Visible = false;
                    ChartAvenueReport.Visible = false;
                    SummaryDIVTOP.Visible = false;
                    SummaryDIVTOP.Style.Add("display", "none");
                    InnerDIV.Style.Add("display", "block");
                    InnerDIV.Visible = true;
                    InnerDIVTOP.Visible = true;

                    BindChartResult(Session["code"].ToString(), Calificacion);
                }
                BindChart();
            }
            else 
            {
                SummaryDIVTOP.Style.Add("display", "block");
            }
             BindChart();
             bindgrid();
        }
        public void bindgrid()
        {
            objDSAll.SelectParameters["code"].DefaultValue = Session["code"].ToString();
            objDSAll.SelectParameters["Date"].DefaultValue = Date;
            objDSAll.SelectParameters["Calificacion"].DefaultValue = Calificacion;
            objDSAll.SelectParameters["Company"].DefaultValue = Session["Company"].ToString();
            radGridAll.DataSourceID = "objDSAll";
            SummaryDIVTOP.Visible = true;
            DataView dvSummaryCount = new DataView();
            dvSummaryCount = (DataView)objDSAll.Select();
            DataTable dtCount = new DataTable();
            dtCount = dvSummaryCount.ToTable();
            ViewState["dvSummaryNewsAlertCount"] = dtCount;
        }

        protected void radGridAll_NeedDataSource(object source, GridNeedDataSourceEventArgs e)
        {
            objDSAll.SelectParameters["code"].DefaultValue = Session["code"].ToString();
            objDSAll.SelectParameters["Date"].DefaultValue = Date;
            objDSAll.SelectParameters["Calificacion"].DefaultValue = Calificacion;
            objDSAll.SelectParameters["Company"].DefaultValue = Session["Company"].ToString();
            radGridAll.DataSourceID = "objDSAll";
            SummaryDIVTOP.Visible = true;
            DataView dvSummaryCount = new DataView();
            dvSummaryCount = (DataView)objDSAll.Select();
            DataTable dtCount = new DataTable();
            dtCount = dvSummaryCount.ToTable();
            ViewState["dvSummaryNewsAlertCount"] = dtCount;
        }
        string target = "";
        public string PDFLink(string pdfname)
        {
            string filename = pdfname.Replace("/", "_").Replace(" ", "") + ".pdf";
            string path = Utility.AppPath + "/File_People_link/" + filename;
            System.IO.FileInfo file = new System.IO.FileInfo(Server.MapPath("~/File_People_link/" + filename));
            if (file.Exists)
            {
                target = "_blank";
                return path;
            }
            else
            {
                target = "";
                return "#";
            }
        }
        public string GetTarget()
        {
            return target;
        }
        protected void ObjDS_SelectingExport(object sender, ObjectDataSourceSelectingEventArgs e)
        {
            if (!e.ExecutingSelectCount)
            {
                e.Arguments.MaximumRows = radGridAll.PageSize;
                e.InputParameters.Add("e", e);
            }
        }
        protected void objDSAll_Selecting(object sender, ObjectDataSourceSelectingEventArgs e)
        {
            if (!e.ExecutingSelectCount)
            {
                e.Arguments.MaximumRows = radGridAll.PageSize;
                e.InputParameters.Add("e", e);
            }
        }
        public void BindChart()
        {
            Color[] barColors = new Color[3] { Color.Green, Color.Red, Color.Blue };
            ReportFuncs rpt = new ReportFuncs();
            DataTable dt = rpt.GetReportAll_NewsAlertChartToday(Session["code"].ToString(), Date, Calificacion, Session["Company"].ToString());
            ViewState["dvSummaryNewsAlertChart"] = dt;
            ChartAvenueReport.DataSource = dt;
            ChartAvenueReport.DataBind();
            if (dt.Rows[0][1].ToString() == "0" && dt.Rows[1][1].ToString() == "0" && dt.Rows[2][1].ToString() == "0")
                ChartAvenueReport.Visible = false;
            ChartAvenueReport.PlotArea.XAxis.AxisLabel.Visible = false;
            int i = 0;

            ChartAvenueReport.PlotArea.XAxis.AutoScale = false;
            ChartAvenueReport.PlotArea.XAxis.AddRange(1, 3, 1);
            foreach (ChartSeriesItem item in ChartAvenueReport.Series[0].Items)
            {
                item.Appearance.FillStyle.MainColor = barColors[i++];
            }
            ChartAvenueReport.PlotArea.XAxis[0].TextBlock.Text = dt.Rows[0][1].ToString();
            ChartAvenueReport.PlotArea.XAxis[1].TextBlock.Text = dt.Rows[1][1].ToString();
            ChartAvenueReport.PlotArea.XAxis[2].TextBlock.Text = dt.Rows[2][1].ToString();
            ChartAvenueReport.Skin = "LightGreen";
        }
        public void BindChartResult(string code1, string Calificacion1)
        {
            Color[] barColors = new Color[3] { Color.Green, Color.Red, Color.Blue };
            ReportFuncs rpt = new ReportFuncs();
            DataTable dt = rpt.GetReportAll_NewsAlertChartToday(code1, Date, Calificacion1, Session["Company"].ToString());
            ViewState["dvSummaryBindChartResult"] = dt;
            RadChartResult.DataSource = dt;
            RadChartResult.DataBind();
            if (dt.Rows[0][1].ToString() == "0" && dt.Rows[1][1].ToString() == "0" && dt.Rows[2][1].ToString() == "0")
                RadChartResult.Visible = false;
            RadChartResult.PlotArea.XAxis.AxisLabel.Visible = false;
            int i = 0;

            RadChartResult.PlotArea.XAxis.AutoScale = false;
            RadChartResult.PlotArea.XAxis.AddRange(1, 3, 1);
            foreach (ChartSeriesItem item in RadChartResult.Series[0].Items)
            {
                item.Appearance.FillStyle.MainColor = barColors[i++];
            }
            RadChartResult.PlotArea.XAxis[0].TextBlock.Text = dt.Rows[0][1].ToString();
            RadChartResult.PlotArea.XAxis[1].TextBlock.Text = dt.Rows[1][1].ToString();
            RadChartResult.PlotArea.XAxis[2].TextBlock.Text = dt.Rows[2][1].ToString();
            RadChartResult.Skin = "LightGreen";
        }
        #region Web Form Designer generated code
        override protected void OnInit(EventArgs e)
        {
            // CODEGEN: This call is required by the ASP.NET Web Form Designer.
            InitializeComponent();
            base.OnInit(e);
        }
        ///        Required method for Designer support - do not modify
        ///        the contents of this method with the code editor.
        /// </summary>
        private void InitializeComponent()
        {
            this.ChartAvenueReport.Click += new Telerik.Web.UI.RadChart.ChartClickEventHandler(this.RadChart1_Click);
        }
        #endregion
        private void RadChart1_Click(object sender, ChartClickEventArgs args)
        {
            objDS.SelectParameters["code"].DefaultValue = Session["code"].ToString();
            objDS.SelectParameters["Date"].DefaultValue = Date;
            objDS.SelectParameters["Calificacion"].DefaultValue = args.SeriesItem.Name;
            objDS.SelectParameters["Company"].DefaultValue = Session["Company"].ToString();
            DataView dvSummary = new DataView();
            dvSummary = (DataView)objDS.Select();
            DataTable dtSummary = new DataTable();
            dtSummary = dvSummary.ToTable();
            ViewState["dvSummaryNewsAlertDetail"] = dtSummary;

            radGrid.DataSourceID = "objDS";

            //PrepareGridColumns();
            radGridAll.Visible = false;
            ChartAvenueReport.Visible = false;
            SummaryDIVTOP.Visible = false;

            InnerDIV.Style.Add("display", "block");

            InnerDIVTOP.Visible = true;
            BindChartResult(Session["code"].ToString(), args.SeriesItem.Name);

        }
        protected void radGrid_ItemCommand(object source, GridCommandEventArgs e)
        {
            GridDataItem item = (GridDataItem)e.Item;
            RadGrid gridComment = (RadGrid)item.FindControl("radGridComment");
            if (e.CommandName == "Comment")
            {
                foreach (GridDataItem radItem in radGrid.Items)
                {
                    ((LinkButton)radItem.FindControl("LnkComment")).Visible = true;
                    ((HtmlGenericControl)radItem.FindControl("CommemtDIV")).Visible = false;
                }
                Label lbl = (Label)item.FindControl("lblNews");

                ((HtmlGenericControl)item.FindControl("CommemtDIV")).Visible = true; ;
                gridComment.DataSourceID = "objDSExport";
                objDSExport.SelectParameters["NewsID"].DefaultValue = lbl.Text;
                ((LinkButton)item.FindControl("LnkComment")).Visible = false; ;
            }
            if (e.CommandName == "ReadMore")
            {
                foreach (GridDataItem radItem in radGrid.Items)
                {
                    ((LinkButton)radItem.FindControl("lnkReadMore")).Visible = true;
                    ((HtmlGenericControl)radItem.FindControl("ReadMoreDiv")).Visible = false;
                    ((Label)radItem.FindControl("lblNewsSummary")).Visible = true;
                }
                SummaryDIVTOP.Visible = false;
                ((HtmlGenericControl)item.FindControl("ReadMoreDiv")).Visible = true;
                ((Label)item.FindControl("lblNewsSummary")).Visible = false;
                ((LinkButton)item.FindControl("lnkReadMore")).Visible = false;
                RadGrid radGridReadMore = (RadGrid)item.FindControl("radGridReadMore");
                sqlDSReadMore.SelectParameters["ID"].DefaultValue = e.CommandArgument.ToString();
                radGridReadMore.DataSourceID = "sqlDSReadMore";
            }
            else if (e.CommandName == "Save")
            {
                Label lbl = (Label)item.FindControl("lblNews");
                TextBox txt = (TextBox)item.FindControl("txtComment");
                TransactionOptions options = new TransactionOptions();
                options.Timeout = new TimeSpan(0, 2, 0);
                options.IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted;
                using (TransactionScope ts = new TransactionScope(TransactionScopeOption.Suppress, options))
                {
                    using (SubSonic.SharedDbConnectionScope conn = new SubSonic.SharedDbConnectionScope(System.Configuration.ConfigurationManager.ConnectionStrings["DBAdex"].ToString()))
                    {
                        //transaction activities.
                        try
                        {
                            if (hdnID.Value == "")
                                hdnID.Value = "0";
                            TblComment prLog = new TblComment();
                            prLog.NewsID = Convert.ToInt32(lbl.Text);
                            prLog.Comments = txt.Text;
                            prLog.RFDate = DateTime.Now;
                            prLog.UserID = Convert.ToInt32(Session["UserID"]);
                            if (hdnID.Value == "0")
                            {
                                hdnID.Value = "0";
                                prLog.Save();
                            }
                            else
                            {
                                TblComment.update(Convert.ToInt32(hdnID.Value), prLog.Comments, Convert.ToInt32(prLog.NewsID));
                                hdnID.Value = "0";
                            }
                            ts.Complete();
                            txt.Text = string.Empty;
                            ((HtmlGenericControl)item.FindControl("CommemtDIV")).Visible = true; ;
                            gridComment.DataSourceID = "objDSExport";
                            objDSExport.SelectParameters["NewsID"].DefaultValue = lbl.Text;
                            ((LinkButton)item.FindControl("LnkComment")).Visible = false; ;

                        }

                        catch (Exception ex2)
                        {
                            ts.Dispose();
                            conn.Dispose();
                        }
                    }
                }
            }
            if (e.CommandName == "Calificación")
            {
                foreach (GridDataItem radItem in radGrid.Items)
                {
                    ((HtmlGenericControl)radItem.FindControl("divCalificación")).Visible = false;
                    ((Label)radItem.FindControl("lblCalification")).Visible = true;
                }
                ((HtmlGenericControl)item.FindControl("divCalificación")).Visible = true;
                ((Label)item.FindControl("lblCalification")).Visible = false;
            }
            if (e.CommandName == "Importancia")
            {
                foreach (GridDataItem radItem in radGrid.Items)
                {
                    ((HtmlGenericControl)radItem.FindControl("divImportancia")).Visible = false;
                    ((Label)radItem.FindControl("lblImportancia")).Visible = true;
                }
                ((HtmlGenericControl)item.FindControl("divImportancia")).Visible = true;
                ((Label)item.FindControl("lblImportancia")).Visible = false;
            }
            if (e.CommandName == "SaveCalificación")
            {
                string ID = ((Label)item.FindControl("lblNews")).Text;
                string Calificación = ((DropDownList)item.FindControl("ddlCalificación")).SelectedItem.Text;
                try
                {
                    TblComment.updateNewsCalificación(Convert.ToInt32(ID), Calificación, "");

                    ((HtmlGenericControl)item.FindControl("divCalificación")).Visible = false;
                    ((Label)item.FindControl("lblCalification")).Visible = true;
                    ((Label)item.FindControl("lblCalification")).Text = ((DropDownList)item.FindControl("ddlCalificación")).SelectedItem.Text;
                }
                catch (Exception ex2)
                {
                }
            }
            if (e.CommandName == "SaveImportancia")
            {
                string ID = ((Label)item.FindControl("lblNews")).Text;
                string Importancia = ((DropDownList)item.FindControl("ddlImportancia")).SelectedItem.Text;
                try
                {
                    TblComment.updateNewsCalificación(Convert.ToInt32(ID), "", Importancia);

                    ((HtmlGenericControl)item.FindControl("divImportancia")).Visible = false;
                    ((Label)item.FindControl("lblImportancia")).Visible = true;
                    ((Label)item.FindControl("lblImportancia")).Text = ((DropDownList)item.FindControl("ddlImportancia")).SelectedItem.Text;
                }
                catch (Exception ex2)
                {
                }
            }
            else if (e.CommandName == "BrandCount")
            {
                ReportFuncs rpt = new ReportFuncs();
                DataTable dt = rpt.GetReportBrandPeople(Convert.ToInt32(e.CommandArgument.ToString()));
                lstBrandsAndPeople.DataSource = dt;
                lstBrandsAndPeople.DataBind();
                ModalPopupExtenderAddSubquestion.Show();
            }
        }
        public void lstBrandsAndPeople_ItemCommand(object sender, ListViewCommandEventArgs e)
        {
            // in assumption that your data item's unique identifier type is Int32


            switch (e.CommandName)
            {

                case "pdfDownload":

                    string filename = e.CommandArgument.ToString().Replace("/", "_").Replace(" ", "") + ".pdf";
                    string path = Server.MapPath("~/File_People_link/" + filename);
                    System.IO.FileInfo file = new System.IO.FileInfo(path);
                    if (file.Exists)
                    {

                        Response.Redirect(Utility.AppPath + "/File_People_link/"+file.Name);
                        //Response.Clear();
                        //Response.AddHeader("Content-Disposition", "attachment; filename=" + file.Name);
                        //Response.AddHeader("Content-Length", file.Length.ToString());
                        //Response.ContentType = "application/octet-stream";
                        //Response.WriteFile(file.FullName);
                        //Response.End();

                    }
                    else
                    {
                        AjaxFuncs.MsgBox(this, "File doesn't exist");
                    }

                    break;
            }
        }
        protected void radGridComment_ItemCommand(object source, GridCommandEventArgs e)
        {
            GridDataItem item = (GridDataItem)e.Item;

            if (e.CommandName == "Edit")
            {
                string lbl = ((Label)item.FindControl("lblComments")).Text;


                //TextBox txt = (TextBox)items.FindControl("txtComment");
                //txt.Text = lbl.Text;
                //string lblID = ((Label)item.FindControl("lblID")).Text;
                //hdnID.te = lblID;
            }
            else if (e.CommandName == "Delete")
            {
                string lbl = ((Label)item.FindControl("lblID")).Text;
                try
                {
                    TblComment.Delete(lbl);
                }
                catch
                {
                    AjaxFuncs.MsgBox(this, "Failed to delete this record, because related data (child records) exists in other tables");
                }

            }
        }
        protected void radGridAll_ItemCommand(object source, GridCommandEventArgs e)
        {


            if (e.CommandName == "GetDetail")
            {
                //Int32 MediaType = Utility.ToInt(e.Item.OwnerTableView.DataKeyValues[e.Item.ItemIndex]["MediaType"].ToString());
                //Int32 CountRecord = Utility.ToInt(e.Item.OwnerTableView.DataKeyValues[e.Item.ItemIndex]["Totalrecord"].ToString());
                //if (CountRecord == 0)
                //{ }
                //else
                //{



                Inngrid();
                //objDS.SelectParameters["MediaTypeCode"].DefaultValue = MediaType.ToString();
                //objDSExport.SelectParameters["MediaTypeCode"].DefaultValue = MediaType.ToString();
                //Session["mtc"] = Convert.ToInt32(MediaType.ToString());

                radGrid.DataSourceID = "objDS";

                //PrepareGridColumns();
                radGridAll.Visible = false;
                ChartAvenueReport.Visible = false;
                SummaryDIVTOP.Visible = false;

                InnerDIV.Style.Add("display", "block");
                InnerDIV.Visible = true;
                InnerDIVTOP.Visible = true;

                BindChartResult(Session["code"].ToString(), Calificacion);

                //}

            }
            else if (e.CommandName == "TV")
            {
                objDSAll.SelectParameters["code"].DefaultValue = "TV";
                radGrid.DataSourceID = "objDSAll";
                SummaryDIVTOP.Visible = true;
                radGridAll.Visible = false;
                ChartAvenueReport.Visible = false;
                SummaryDIVTOP.Visible = false;
                InnerDIV.Visible = true;
                InnerDIV.Style.Add("display", "block");

                InnerDIVTOP.Visible = true;
                DataView dvSummary = new DataView();
                dvSummary = (DataView)objDSAll.Select();
                DataTable dtSummary = new DataTable();
                dtSummary = dvSummary.ToTable();
                ViewState["dvSummaryNewsAlertDetail"] = dtSummary;
                BindChartResult("TV", Calificacion);
            }
            else if (e.CommandName == "Radio")
            {
                objDSAll.SelectParameters["code"].DefaultValue = "Radio";
                radGrid.DataSourceID = "objDSAll";
                SummaryDIVTOP.Visible = true;
                radGridAll.Visible = false;
                ChartAvenueReport.Visible = false;
                SummaryDIVTOP.Visible = false;
                InnerDIV.Visible = true;
                InnerDIV.Style.Add("display", "block");

                InnerDIVTOP.Visible = true;
                DataView dvSummary = new DataView();
                dvSummary = (DataView)objDSAll.Select();
                DataTable dtSummary = new DataTable();
                dtSummary = dvSummary.ToTable();
                ViewState["dvSummaryNewsAlertDetail"] = dtSummary;
                BindChartResult("Radio", Calificacion);
            }
            else if (e.CommandName == "Press")
            {
                objDSAll.SelectParameters["code"].DefaultValue = "Press";
                radGrid.DataSourceID = "objDSAll";
                SummaryDIVTOP.Visible = true;
                radGridAll.Visible = false;
                ChartAvenueReport.Visible = false;
                SummaryDIVTOP.Visible = false;
                InnerDIV.Visible = true;
                InnerDIV.Style.Add("display", "block");

                InnerDIVTOP.Visible = true;
                DataView dvSummary = new DataView();
                dvSummary = (DataView)objDSAll.Select();
                DataTable dtSummary = new DataTable();
                dtSummary = dvSummary.ToTable();
                ViewState["dvSummaryNewsAlertDetail"] = dtSummary;
                BindChartResult("Press", Calificacion);
            }
            else if (e.CommandName == "Internet")
            {
                objDSAll.SelectParameters["code"].DefaultValue = "Internet";
                radGrid.DataSourceID = "objDSAll";
                SummaryDIVTOP.Visible = true;
                radGridAll.Visible = false;
                ChartAvenueReport.Visible = false;
                SummaryDIVTOP.Visible = false;
                InnerDIV.Visible = true;
                InnerDIV.Style.Add("display", "block");

                InnerDIVTOP.Visible = true;
                DataView dvSummary = new DataView();
                dvSummary = (DataView)objDSAll.Select();
                DataTable dtSummary = new DataTable();
                dtSummary = dvSummary.ToTable();
                ViewState["dvSummaryNewsAlertDetail"] = dtSummary;
                BindChartResult("Internet", Calificacion);
            }
            //end GetDetail
        }
        protected void ObjDS_Selecting(object sender, ObjectDataSourceSelectingEventArgs e)
        {
            if (!e.ExecutingSelectCount)
            {
                e.Arguments.MaximumRows = radGrid.PageSize;
                e.InputParameters.Add("e", e);
            }
        }
        protected void ObjDSread_Selecting(object sender, ObjectDataSourceSelectingEventArgs e)
        {
            if (!e.ExecutingSelectCount)
            {
                e.Arguments.MaximumRows = radGrid.PageSize;
                e.InputParameters.Add("e", e);
            }
        }
        protected void radGrid_NeedDataSource(object source, GridNeedDataSourceEventArgs e)
        {
            objDS.SelectParameters["code"].DefaultValue = Session["code"].ToString();
            objDS.SelectParameters["Date"].DefaultValue = Date;
            objDS.SelectParameters["Calificacion"].DefaultValue = Calificacion;
            objDS.SelectParameters["Company"].DefaultValue = Session["Company"].ToString();
            DataView dvSummary = new DataView();
            dvSummary = (DataView)objDS.Select();
            DataTable dtSummary = new DataTable();
            dtSummary = dvSummary.ToTable();
            ViewState["dvSummaryNewsAlertDetail"] = dtSummary;


        }
        private void Inngrid()
        {


            objDS.SelectParameters["code"].DefaultValue = "";
            objDS.SelectParameters["Date"].DefaultValue = Date;
            objDS.SelectParameters["Calificacion"].DefaultValue = Calificacion;
            objDS.SelectParameters["Company"].DefaultValue = Session["Company"].ToString();
            DataView dvSummary = new DataView();
            dvSummary = (DataView)objDS.Select();
            DataTable dtSummary = new DataTable();
            dtSummary = dvSummary.ToTable();
            ViewState["dvSummaryNewsAlertDetail"] = dtSummary;
        }
        protected void lnlBack_Clicked(object sender, System.EventArgs e)
        {
            InnerDIV.Style.Add("display", "none");
            InnerDIVTOP.Visible = false;
            radGridAll.Visible = true;
            SummaryDIVTOP.Visible = true;
            ChartAvenueReport.Visible = true;

        }
        protected void btnExportSummary_Click(object sender, EventArgs e)
        {
            DataTable dtDetails = (DataTable)ViewState["dvSummaryNewsAlertDetail"];

            if (!dtDetails.Columns.Contains("Date"))
                dtDetails.Columns.Add("Date");
            if (dtDetails.Columns.Contains("RFDate"))
            {
                foreach (DataRow dr in dtDetails.Rows)
                {
                    string date = dr["RFDate"].ToString();
                    dr["Date"] = date.Substring(0, 10);
                    //dr["Date"] = String.Format("{0:dd.MM.yyyy}", dr["RFDate"]);
                }
            }
            if (dtDetails.Columns.Contains("ID"))
            {
                dtDetails.Columns.Remove("ID");
                dtDetails.Columns.Remove("MediaTypeName");
                dtDetails.Columns.Remove("MediaTypeCode");
                dtDetails.Columns.Remove("MediaCode");
                dtDetails.Columns.Remove("SectionCode");
                dtDetails.Columns.Remove("Totalrecord");
                dtDetails.Columns.Remove("SumColumn");
                dtDetails.Columns.Remove("RFDate"); 

            }

            DumpExcelSummary((DataTable)ViewState["dvSummaryNewsAlertCount"], (DataTable)ViewState["dvSummaryNewsAlertChart"], dtDetails);
        }
        private void DumpExcelSummary(DataTable tblCount, DataTable tblChart, DataTable tbldetails)
        {

            Int32 tblColumnCount = tblCount.Columns.Count;
            int row = tblChart.Rows.Count;
            if (tblColumnCount > 0)
            {

                using (ExcelPackage pck = new ExcelPackage())
                {

                    try
                    {

                        //Create the worksheet
                        ExcelWorksheet ws = pck.Workbook.Worksheets.Add("MediaMonitor-News&Comments");


                        ws.Cells["A1"].LoadFromDataTable(tblCount, true);

                        ws.Cells["A25"].LoadFromDataTable(tblChart, true);
                        ws.Cells["A30"].LoadFromDataTable(tbldetails, true);
                        ws.DefaultColWidth = 20;
                        using (ExcelRange rngSummary = ws.Cells["A1:D1"])
                        {
                            rngSummary.Style.Font.Bold = true;
                        }

                        using (ExcelRange rngUserSelection = ws.Cells["A30:O30"])
                        {
                            rngUserSelection.Style.Font.Bold = true;
                        }
                        ws.Cells["A30:T30"].AutoFilter = true;

                        var BarChart = ws.Drawings.AddChart("Competition Chart", eChartType.ColumnClustered) as ExcelBarChart;
                        //Set top left corner to row 1 column 2
                        BarChart.SetPosition(3, 2, 2, 2);
                        BarChart.SetSize(800, 400);
                        BarChart.Title.Text = "News & comments calification";
                        BarChart.Style = eChartStyle.Style26;
                        BarChart.Legend.Remove();
                        BarChart.DataLabel.ShowValue = true;
                        BarChart.DataLabel.Font.Bold = true;
                        BarChart.DataLabel.Border.LineCap = OfficeOpenXml.Drawing.eLineCap.Round;
                        BarChart.Series.Add(ExcelRange.GetAddress(26, 2, 28, 2), ExcelRange.GetAddress(26, 1, 28, 1));
                    }
                    catch { }
                    Response.Clear();
                    Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                    Response.AddHeader("content-disposition", "attachment;  filename=MediaMonitor-News&Comments.xlsx");
                    Response.BinaryWrite(pck.GetAsByteArray());
                    Response.End();
                }
            }
        }
        protected void btnExport_Click(object sender, EventArgs e)
        {
            DataTable dtDetails = (DataTable)ViewState["dvSummaryNewsAlertDetail"];
            if (!dtDetails.Columns.Contains("Date"))
                dtDetails.Columns.Add("Date");
            if (dtDetails.Columns.Contains("RFDate"))
            {
                foreach (DataRow dr in dtDetails.Rows)
                {
                    string date = dr["RFDate"].ToString();
                    dr["Date"] = date.Substring(0, 10);
                    //dr["Date"] = String.Format("{0:dd.MM.yyyy}", dr["RFDate"]);
                }
            }
            if (dtDetails.Columns.Contains("ID"))
            {
                dtDetails.Columns.Remove("ID");
                dtDetails.Columns.Remove("MediaTypeName");
                dtDetails.Columns.Remove("MediaTypeCode");
                dtDetails.Columns.Remove("MediaCode");
                dtDetails.Columns.Remove("SectionCode");
                dtDetails.Columns.Remove("Totalrecord");
                dtDetails.Columns.Remove("SumColumn");
               
                dtDetails.Columns.Remove("RFDate"); 
            }
            DataTable tblcount = (DataTable)ViewState["dvSummaryBindChartResult"];
            DumpSummary(dtDetails, tblcount);
        }
        private void DumpSummary(DataTable tbldetails, DataTable tblChart)
        {

            Int32 tblColumnCount = tbldetails.Columns.Count;
            int row = tbldetails.Rows.Count;
            if (tblColumnCount > 0)
            {

                using (ExcelPackage pck = new ExcelPackage())
                {

                    try
                    {

                        //Create the worksheet
                        ExcelWorksheet ws = pck.Workbook.Worksheets.Add("MediaMonitor-News&Comments");


                        ws.Cells["A25"].LoadFromDataTable(tblChart, true);
                        ws.Cells["A30"].LoadFromDataTable(tbldetails, true);
                        ws.DefaultColWidth = 20;
                        using (ExcelRange rngSummary = ws.Cells["A1:D1"])
                        {
                            rngSummary.Style.Font.Bold = true;
                        }
                        using (ExcelRange rngUserSelection = ws.Cells["A25:O25"])
                        {
                            rngUserSelection.Style.Font.Bold = true;
                        }
                        var BarChart = ws.Drawings.AddChart("Competition Chart", eChartType.ColumnClustered) as ExcelBarChart;
                        //Set top left corner to row 1 column 2
                        BarChart.SetPosition(3, 2, 2, 2);
                        BarChart.SetSize(800, 400);
                        BarChart.Title.Text = "News & comments calification";
                        BarChart.Style = eChartStyle.Style26;
                        BarChart.Legend.Remove();
                        BarChart.DataLabel.ShowValue = true;
                        BarChart.DataLabel.Font.Bold = true;
                        BarChart.DataLabel.Border.LineCap = OfficeOpenXml.Drawing.eLineCap.Round;
                        BarChart.Series.Add(ExcelRange.GetAddress(26, 2, 28, 2), ExcelRange.GetAddress(26, 1, 28, 1));


                    }
                    catch { }
                    Response.Clear();
                    Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                    Response.AddHeader("content-disposition", "attachment;  filename=MediaMonitor-News&Comments.xlsx");
                    Response.BinaryWrite(pck.GetAsByteArray());
                    Response.End();
                }
            }
        }
        protected void ddlMediaType_SelectedIndexChanged(object sender, EventArgs e)
        {
            string text = this.ddlMediaType.SelectedItem.Text;
            if (text != null)
            {
                if (text == "ALL")
                {
                    this.Inngrid();
                    this.radGrid.DataSourceID = "objDS";
                    this.radGridAll.Visible = false;
                    this.ChartAvenueReport.Visible = false;
                    this.SummaryDIVTOP.Visible = false;
                    this.InnerDIV.Style.Add("display", "block");
                    this.InnerDIV.Visible = true;
                    this.InnerDIVTOP.Visible = true;
                    this.BindChartResult(this.Session["code"].ToString(), this.Calificacion);
                }
                else
                {
                    if (text == "INTERNET")
                    {
                        this.objDSAll.SelectParameters["code"].DefaultValue = "Internet";
                        this.radGrid.DataSourceID = "objDSAll";
                        this.SummaryDIVTOP.Visible = true;
                        this.radGridAll.Visible = false;
                        this.ChartAvenueReport.Visible = false;
                        this.SummaryDIVTOP.Visible = false;
                        this.InnerDIV.Visible = true;
                        this.InnerDIV.Style.Add("display", "block");
                        this.InnerDIVTOP.Visible = true;
                        DataView dvSummary = new DataView();
                        dvSummary = (DataView)this.objDSAll.Select();
                        DataTable dtSummary = new DataTable();
                        dtSummary = dvSummary.ToTable();
                        this.ViewState["dvSummaryNewsAlertDetail"] = dtSummary;
                        this.BindChartResult("Internet", this.Calificacion);
                    }
                    else
                    {
                        if (text == "RADIO")
                        {
                            this.objDSAll.SelectParameters["code"].DefaultValue = "Radio";
                            this.radGrid.DataSourceID = "objDSAll";
                            this.SummaryDIVTOP.Visible = true;
                            this.radGridAll.Visible = false;
                            this.ChartAvenueReport.Visible = false;
                            this.SummaryDIVTOP.Visible = false;
                            this.InnerDIV.Visible = true;
                            this.InnerDIV.Style.Add("display", "block");
                            this.InnerDIVTOP.Visible = true;
                            DataView dvSummary1 = new DataView();
                            dvSummary1 = (DataView)this.objDSAll.Select();
                            DataTable dtSummary1 = new DataTable();
                            dtSummary1 = dvSummary1.ToTable();
                            this.ViewState["dvSummaryNewsAlertDetail"] = dtSummary1;
                            this.BindChartResult("Radio", this.Calificacion);
                        }
                        else
                        {
                            if (text == "TV")
                            {
                                this.objDSAll.SelectParameters["code"].DefaultValue = "TV";
                                this.radGrid.DataSourceID = "objDSAll";
                                this.SummaryDIVTOP.Visible = true;
                                this.radGridAll.Visible = false;
                                this.ChartAvenueReport.Visible = false;
                                this.SummaryDIVTOP.Visible = false;
                                this.InnerDIV.Visible = true;
                                this.InnerDIV.Style.Add("display", "block");
                                this.InnerDIVTOP.Visible = true;
                                DataView dvSummary2 = new DataView();
                                dvSummary2 = (DataView)this.objDSAll.Select();
                                DataTable dtSummary2 = new DataTable();
                                dtSummary2 = dvSummary2.ToTable();
                                this.ViewState["dvSummaryNewsAlertDetail"] = dtSummary2;
                                this.BindChartResult("TV", this.Calificacion);
                            }
                            else
                            {
                                if (text == "PRESS")
                                {
                                    this.objDSAll.SelectParameters["code"].DefaultValue = "Press";
                                    this.radGrid.DataSourceID = "objDSAll";
                                    this.SummaryDIVTOP.Visible = true;
                                    this.radGridAll.Visible = false;
                                    this.ChartAvenueReport.Visible = false;
                                    this.SummaryDIVTOP.Visible = false;
                                    this.InnerDIV.Visible = true;
                                    this.InnerDIV.Style.Add("display", "block");
                                    this.InnerDIVTOP.Visible = true;
                                    DataView dvSummary3 = new DataView();
                                    dvSummary3 = (DataView)this.objDSAll.Select();
                                    DataTable dtSummary3 = new DataTable();
                                    dtSummary3 = dvSummary3.ToTable();
                                    this.ViewState["dvSummaryNewsAlertDetail"] = dtSummary3;
                                    this.BindChartResult("Press", this.Calificacion);
                                }
                            }
                        }
                    }
                }
            }
        }

        protected void ddlSortNews_SelectedIndexChanged(object sender, EventArgs e)
        {
            string text = this.ddlSortNews.SelectedItem.Text;
            if (text != null)
            {
                if (text == "Media Type")
                {
                    this.objDSAll.SelectParameters["code"].DefaultValue = "MediaType";
                    this.radGrid.DataSourceID = "objDSAll";
                    this.SummaryDIVTOP.Visible = true;
                    this.radGridAll.Visible = false;
                    this.ChartAvenueReport.Visible = false;
                    this.SummaryDIVTOP.Visible = false;
                    this.InnerDIV.Visible = true;
                    this.InnerDIV.Style.Add("display", "block");
                    this.InnerDIVTOP.Visible = true;
                    DataView dvSummary3 = new DataView();
                    dvSummary3 = (DataView)this.objDSAll.Select();
                    DataTable dtSummary3 = new DataTable();
                    dtSummary3 = dvSummary3.ToTable();
                    this.ViewState["dvSummaryNewsAlertDetail"] = dtSummary3;
                    this.BindChartResult("MediaType", this.Calificacion);
                }
                else
                {
                    if (text == "Title")
                    {
                        this.objDSAll.SelectParameters["code"].DefaultValue = "Title";
                        this.radGrid.DataSourceID = "objDSAll";
                        this.SummaryDIVTOP.Visible = true;
                        this.radGridAll.Visible = false;
                        this.ChartAvenueReport.Visible = false;
                        this.SummaryDIVTOP.Visible = false;
                        this.InnerDIV.Visible = true;
                        this.InnerDIV.Style.Add("display", "block");
                        this.InnerDIVTOP.Visible = true;
                        DataView dvSummary = new DataView();
                        dvSummary = (DataView)this.objDSAll.Select();
                        DataTable dtSummary = new DataTable();
                        dtSummary = dvSummary.ToTable();
                        this.ViewState["dvSummaryNewsAlertDetail"] = dtSummary;
                        this.BindChartResult("Title", this.Calificacion);
                    }
                    else
                    {
                        if (text == "Chronological")
                        {
                            this.objDSAll.SelectParameters["code"].DefaultValue = "Chronological";
                            this.radGrid.DataSourceID = "objDSAll";
                            this.SummaryDIVTOP.Visible = true;
                            this.radGridAll.Visible = false;
                            this.ChartAvenueReport.Visible = false;
                            this.SummaryDIVTOP.Visible = false;
                            this.InnerDIV.Visible = true;
                            this.InnerDIV.Style.Add("display", "block");
                            this.InnerDIVTOP.Visible = true;
                            DataView dvSummary1 = new DataView();
                            dvSummary1 = (DataView)this.objDSAll.Select();
                            DataTable dtSummary1 = new DataTable();
                            dtSummary1 = dvSummary1.ToTable();
                            this.ViewState["dvSummaryNewsAlertDetail"] = dtSummary1;
                            this.BindChartResult("Chronological", this.Calificacion);
                        }
                        else
                        {
                            if (text == "Most Recent")
                            {
                                this.objDSAll.SelectParameters["code"].DefaultValue = "MostRecent";
                                this.radGrid.DataSourceID = "objDSAll";
                                this.SummaryDIVTOP.Visible = true;
                                this.radGridAll.Visible = false;
                                this.ChartAvenueReport.Visible = false;
                                this.SummaryDIVTOP.Visible = false;
                                this.InnerDIV.Visible = true;
                                this.InnerDIV.Style.Add("display", "block");
                                this.InnerDIVTOP.Visible = true;
                                DataView dvSummary2 = new DataView();
                                dvSummary2 = (DataView)this.objDSAll.Select();
                                DataTable dtSummary2 = new DataTable();
                                dtSummary2 = dvSummary2.ToTable();
                                this.ViewState["dvSummaryNewsAlertDetail"] = dtSummary2;
                                this.BindChartResult("MostRecent", this.Calificacion);
                            }
                        }
                    }
                }
            }
        }
    }
}